import os
import datetime
import logging.handlers
import pymysql

# 设置数据库连接参数
config = {
    'user': 'root',
    'password': 'your_password',
    'host': 'localhost',
    'database': 'your_database'
}

# 获取当前日期作为新表名后缀 
today = datetime.datetime.now().strftime('%Y%m%d')

try:
    # 连接到数据库服务器并创建游标对象 
    conn = pymysql.connect(**config)
    cursor = conn.cursor()

   
    # 重命名旧表以备份和归档，然后清空原始表中所有数据行。 
    old_table_name = "bos_order"

    # 生成备份文件名：在原始名称基础上添加时间戳 
    backup_file_name = f"{old_table_name}_{datetime.datetime.now().strftime('%Y%m%d%H%M%S')}"

    # 执行重命名操作，将旧表改名为备份文件名称 
    rename_query = f"RENAME TABLE {old_table_name} TO {backup_file_name}"
    cursor.execute(rename_query)

    # 创建一个新表同步字段和索引
    create_new_bos_order_query = "CREATE TABLE bos_order LIKE {old_table_name}"
    cursor.execute(create_new_bos_order_query, (backup_file_name,))
    # 
     # 创建新表并插入需要保留的数据行（此处省略）
    # INSERT INTO my_table_2022-01-01 SELECT * FROM my_table WHERE date_column < '2022-01-01';


    # 记录成功信息到日志文件并输出到终端   
    log_message = f"bos_order表已成功归档并备份到{backup_file_name}"

    logging.basicConfig(
        level=logging.INFO,
        format='%(asctime)s %(levelname)s: %(message)s',
        handlers=[
            logging.StreamHandler(),
            logging.handlers.RotatingFileHandler(
                'bos_order_archive.log', maxBytes=1024 * 1024 * 100, backupCount=3
            )
        ]
    )

    logger = logging.getLogger('my_logger')

    logger.info(log_message)

except pymysql.Error as error:
# 处理异常情况，并记录错误信息到日志文件和控制台 
log_message = f"Failed to archive bos_order table: {error}"

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s %(levelname)s: %(message)s',
    handlers=[
        logging.StreamHandler(),
        logging.handlers.RotatingFileHandler(
            'bos_order_archive.log', maxBytes=1024 * 1024 * 100, backupCount=3
        )
    ]
)

logger = logging.getLogger('my_logger')

logger.error(log_message)

print(f"{datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')}: {log_message}")

finally:
# 关闭数据库连接和游标对象 
cursor.close()
conn.close()
